package com.merck.service.export;

import com.amazonaws.HttpMethod;
import com.amazonaws.services.s3.AmazonS3;
import com.amazonaws.services.s3.AmazonS3ClientBuilder;
import com.amazonaws.services.s3.model.*;
import com.amazonaws.services.s3.model.CannedAccessControlList;
import com.amazonaws.services.s3.model.GeneratePresignedUrlRequest;
import com.amazonaws.services.s3.model.PutObjectRequest;
import com.merck.service.impl.ImportDataServiceImpl;
import com.merck.service.read.ReadExcelImpl;
import com.merck.utils.DateUtils;
import com.merck.utils.SendMail;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 *
 * 导出月度报表
 */
public class GenerateExcelImpl {

  private Logger log = Logger.getLogger(GenerateExcelImpl.class);

  public void exportExcel() {

    Properties pro = new Properties();
    Properties pros = new Properties();
    InputStream resourceAsStream = null;
    AmazonS3 s3Client = null;
    InputStream configStream = null;
    String configBucketName = null;
    String configRemoteFileName = null;
    try {
      configStream = GenerateExcelImpl.class.getClassLoader().getResourceAsStream("db.properties");
      pros.load(configStream);
      configBucketName = (String) pros.get("config.bucket");
      configRemoteFileName = (String) pros.get("config.remote");
      System.out.println("读取配置文件开始-----------");
//      resourceAsStream = ReadExcelImpl.class.getResourceAsStream("/config.properties");
      s3Client = openS3ClientByEC2();
      GetObjectRequest getObjectRequest = new GetObjectRequest(configBucketName, configRemoteFileName);
      S3Object s3Object = s3Client.getObject(getObjectRequest);
      resourceAsStream = s3Object.getObjectContent();
      pro.load(resourceAsStream);
      System.out.println("读取配置文件结束-----------");

      String bucketName;
      ImportDataServiceImpl importData = new ImportDataServiceImpl();
      //SHEET 页对应的数据sql
      Map<String, String> map = null;
      Map<String, Map<String, String>> mapTitle = null;
      //到出的Excel名称
      //当年 2021
      //从表中获取年份和季度
      String sql = "select * from lego_kpi_calculation.ads_kpi_final_report_qf where year_id is not null and quarter_id is not null limit 1";
      List<Map<String, Object>> list = importData.queryForListMap(sql);
      if (CollectionUtils.isEmpty(list)) {
        throw new RuntimeException("数据的年和季度为空！");
      }
      String quarterId = (String)list.get(0).get("quarter_id");
      String currentYear = quarterId.substring(0, 4);
      String currentQuarterly = quarterId.substring(4);

//      String currentYear = DateUtils.getCurrentYear();
//      String currentQuarterly = "Q" + DateUtils.getCurrentQuarterly();
      String dateStr = DateUtils.format(new Date(), "yyyyMMddHHmmss");
      boolean delete;
      String name = currentYear + " " + currentQuarterly;
//导出路径
      String remoteFileName = null;
      String backupPath = (String) pro.get("backup.report.export.file.name") + "backup" + dateStr + "/" + currentYear + " " + currentQuarterly;
      String exportPath = (String) pro.get("report.export.file.name") + currentYear + " " + currentQuarterly; /**
       * 导出Qutly KPI report FBU
       */
//        mapTitle = TitleMap.getTitleMap("QFBU");
//        map = new LinkedHashMap<>();
//        map.put("KPI_FBU_REP_FD_C FD_S FD_P", SqlStr.KPI_FBU_REP_FD_C_FD_S_FD_P);
//        map.put("KPI_FBU_DSM_FD_BA", SqlStr.KPI_FBU_DSM_FD_BA);
//        map.put("KPI_FBU_DSM_FD_S FD_C FD_P", SqlStr.KPI_FBU_DSM_FD_S_FD_C_FD_P);
//        map.put("KPI_FBU_FT DM_FT", SqlStr.KPI_FBU_FT_DM_FT);
//        map.put("KPI_FBU_RSD&NAD", SqlStr.KPI_FBU_RSD_NAD);
//
//
//        /**将excel生成进S3*/
//        file = new File("temp.xlsx");
//        log.info("开始生成excel表格");
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);
//        //导出
//        bucketName = (String) pro.get("report.export.bucket.name");
//        remoteFileName =exportPath + " Quarterly KPI Report FBU.xlsx";
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
////        备份
//        bucketName = (String) pro.get("backup.report.export.bucket.name");
//        remoteFileName = backupPath + " Quarterly KPI Report FBU.xlsx";
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        delete = file.delete();
//
//        //S3环境运行代码结束
//
//        /**
//         * 导出Quarterly KPI Report GBU
//         */
//        mapTitle = TitleMap.getTitleMap("QGBU");
//        map = new LinkedHashMap<>();
//        map.put("KPI_GBU_REP_CHD",SqlStr.KPI_GBU_REP_CHD);
//        map.put("KPI_GBU_REP_DB_CV_MIX", SqlStr.KPI_GBU_REP_DB_CV_MIX);
//        map.put("KPI_GBU_REP_TD_ST", SqlStr.KPI_GBU_REP_TD_ST);
//        map.put("KPI_GBU_DSM_HA", SqlStr.KPI_GBU_DSM_HA);
//        map.put("KPI_GBU_DSM_ST_TD", SqlStr.KPI_GBU_DSM_ST_TD);
//        map.put("KPI_GBU_DSM_DB_CV_MIX_CHD", SqlStr.KPI_GBU_DSM_DB_CV_MIX_CHD);
//        map.put("KPI_GBU_RSM", SqlStr.KPI_GBU_RSM);
//
//        file = new File("temp.xlsx");
//        //导出
//        bucketName = (String) pro.get("report.export.bucket.name");
//        remoteFileName = exportPath + " Quarterly KPI Report GBU.xlsx";
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        //备份
//        bucketName = (String) pro.get("backup.report.export.bucket.name");
//        remoteFileName = backupPath + " Quarterly KPI Report GBU.xlsx";
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        delete = file.delete();
//        /**
//         * 导出季度报表 Quarterly KPI Report KA
//         */
//            mapTitle = TitleMap.getTitleMap("QKA");
//        map = new LinkedHashMap<>();
//        map.put("KPI_KA_DSM_KA",SqlStr.KPI_KA_DSM_KA);
//        //导出
//        file = new File("temp.xlsx");
//        bucketName = (String) pro.get("report.export.bucket.name");
//        remoteFileName =exportPath + " Quarterly KPI Report KA.xlsx";
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        //备份
//        bucketName = (String) pro.get("backup.report.export.bucket.name");
//        remoteFileName = backupPath + " Quarterly KPI Report KA.xlsx";
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        delete = file.delete();
//
// /**
//         * 导出季度报表 Quarterly KPI Report OBU
//         */
//            mapTitle = TitleMap.getTitleMap("QOBU");
//        map = new LinkedHashMap<>();
//        map.put("KPI_OBU_REP_Erbitux",SqlStr.KPI_OBU_REP_Erbitux);
//        map.put("KPI_OBU_REP_Xalkori",SqlStr.KPI_OBU_REP_Xalkori);
//        map.put("KPI_OBU_DSM",SqlStr.KPI_OBU_DSM);
//        map.put("KPI_OBU_RSM",SqlStr.KPI_OBU_RSM);
//        file = new File("temp.xlsx");
//        //导出
//        remoteFileName =exportPath + " Quarterly KPI Report OBU.xlsx";
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);
//        bucketName = (String) pro.get("report.export.bucket.name");
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        //备份
//        bucketName = (String) pro.get("backup.report.export.bucket.name");
//        remoteFileName = backupPath + " Quarterly KPI Report OBU.xlsx";
//        GenerateExcelIntoS3(remoteFileName,bucketName,file);
//        delete = file.delete();

      List<File> attachments = new ArrayList<>();
      /**
       * 导出月份报表
       */
      //导出 Monthly KPI Report FBU
      mapTitle = TitleMap.getMonthTitleMap("MFBU");
      map = new LinkedHashMap<>();
      map.put("KPI_FBU_REP_FD_C FD_S FD_P", SqlStr.KPI_FBU_REP_FD_C_FD_S_FD_P_M);
      map.put("KPI_FBU_DSM_FD_BA", SqlStr.KPI_FBU_DSM_FD_BA_M);
      map.put("KPI_FBU_DSM_FD_S FD_C FD_P", SqlStr.KPI_FBU_DSM_FD_S_FD_C_FD_P_M);
//      map.put("KPI_FBU_RSD&NAD", SqlStr.KPI_FBU_RSD_NAD_M);
      //导出
      File file1 = new File(name + " Monthly KPI Report FBU.xlsx");
      attachments.add(file1);
      bucketName = (String) pro.get("report.export.bucket.name");
      remoteFileName = exportPath + " Monthly KPI Report FBU.xlsx";
      importData.exportDataExcleApp(map, file1, "excle", mapTitle, null);
      GenerateExcelIntoS3(remoteFileName, bucketName, file1);
      //备份
      bucketName = (String) pro.get("backup.report.export.bucket.name");
      remoteFileName = backupPath + " Monthly KPI Report FBU.xlsx";
      GenerateExcelIntoS3(remoteFileName, bucketName, file1);
      log.info("");

      //导出 Monthly KPI Report GBU
      mapTitle = TitleMap.getMonthTitleMap("MGBU");
      map = new LinkedHashMap<>();
      map.put("KPI_GBU_REP_CHD", SqlStr.KPI_GBU_REP_CHD_M);
      map.put("KPI_GBU_REP_DB_CV_MIX_TD_ST", SqlStr.KPI_GBU_REP_DB_CV_MIX_TD_ST);
      map.put("KPI_GBU_DSM_HA", SqlStr.KPI_GBU_DSM_HA_M);
      map.put("KPI_GBU_DSM_ST_TD_DB_CV_MIX_CHD", SqlStr.KPI_GBU_DSM_ST_TD_DB_CV_MIX_CHD);
      //导出
      File file2 = new File(name + " Monthly KPI Report GBU.xlsx");
      attachments.add(file2);
      bucketName = (String) pro.get("report.export.bucket.name");
      remoteFileName = exportPath + " Monthly KPI Report GBU.xlsx";
      importData.exportDataExcleApp(map, file2, "excle", mapTitle, null);
      GenerateExcelIntoS3(remoteFileName, bucketName, file2);
      //备份
      bucketName = (String) pro.get("backup.report.export.bucket.name");
      remoteFileName = backupPath + " Monthly KPI Report GBU.xlsx";
      GenerateExcelIntoS3(remoteFileName, bucketName, file2);

      //导出 Monthly KPI Report OBU
      mapTitle = TitleMap.getMonthTitleMap("MOBU");
      map = new LinkedHashMap<>();
      map.put("KPI_OBU_REP_Erbitux", SqlStr.KPI_OBU_REP_Erbitux_M);
      map.put("KPI_OBU_REP_Xalkori", SqlStr.KPI_OBU_REP_Xalkori_M);
      map.put("KPI_OBU_DSM", SqlStr.KPI_OBU_DSM_M);
      //导出
      File file3 = new File(name + " Monthly KPI Report OBU.xlsx");
      attachments.add(file3);
      bucketName = (String) pro.get("report.export.bucket.name");
      remoteFileName = exportPath + " Monthly KPI Report OBU.xlsx";
      importData.exportDataExcleApp(map, file3, "excle", mapTitle, null);
      GenerateExcelIntoS3(remoteFileName, bucketName, file3);
      //备份
      bucketName = (String) pro.get("backup.report.export.bucket.name");
      remoteFileName = backupPath + " Monthly KPI Report OBU.xlsx";
      GenerateExcelIntoS3(remoteFileName, bucketName, file3);
      String currentDateAsString = DateUtils.getCurrentDateAsString();
      SendMail.sendMail(null, null, "导出月度报表数据任务在 " + currentDateAsString + " 运行结果通知", SqlStr.SUCESS_SEND_CONTENT, attachments);
      file1.delete();
      file2.delete();
      file3.delete();
    } catch (Exception e) {
      System.out.println("读取配置文件异常-----------");
      log.error(e.getMessage());
      String currentDateAsString = DateUtils.getCurrentDateAsString();
      SendMail.sendMail(null, null, "导出月度报表数据任务在 " + currentDateAsString + " 运行结果通知", SqlStr.FAILURE_SEND_CONTENT, null);
    } finally {
      if (resourceAsStream != null) {
        try {
          resourceAsStream.close();
        } catch (IOException e) {
          log.error(e.getMessage());
        }
      }
    }

  }

  private void GenerateExcelIntoS3(String remoteFileName, String bucketName, File file) {
//        File file = new File("temp.xlsx");
//        log.info("开始生成excel表格");
//        String bucketName = (String) pro.get("export.bucket.name");
//        importData.exportDataExcleApp(map, file, "excle", mapTitle, null);

    //S3环境运行代码开始
//        //把生成的Excel上传到S3，上传的路径为上面两个参数定义 ，为 s3://qa-lego-deployment/templates/ + fileName
//        //可以在HUE中查看
    AmazonS3 s3Client = null;
    try {
      s3Client = openS3ClientByEC2();

      PutObjectRequest poRequest = new PutObjectRequest(bucketName, remoteFileName, file).withCannedAcl(CannedAccessControlList.Private);
      s3Client.putObject(poRequest);
      GeneratePresignedUrlRequest urlRequest = new GeneratePresignedUrlRequest(bucketName, remoteFileName, HttpMethod.POST);
      s3Client.generatePresignedUrl(urlRequest);
//            file.delete();
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new RuntimeException("将文件写入S3出错" + e.getMessage());
    } finally {
      if (s3Client != null) {
        s3Client.shutdown();
      }
    }
  }

  public static AmazonS3 openS3ClientByEC2() {
    AmazonS3 s3Client = null;
    try {
      s3Client = AmazonS3ClientBuilder.defaultClient();
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
    return s3Client;
  }


}
